package com.mbbmap.app.home;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.util.Constants;


public class SecAuditTrailHome {

	/**
	 * @param args
	 */
	public static Date timenow = new Date();
	static DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
	static DateFormat tm = new SimpleDateFormat("hh.mm");
	static String todaydate= df.format(new Date());
	static String todaytime= tm.format(new Date());
	
	public ArrayList FillDataTable(String recordsearch, String datefrom, String dateto){
	
	ResultSet rsrecord = null;
	ArrayList<String> recordresult = new ArrayList<String>();
	DateFormat dateformat = new SimpleDateFormat("dd/MMM/yyyy hh:mm a");
	
	String dayfrom = "";
	String monthfrom = "";
	String yearfrom = "";
	String dayto = "";
	String monthto = "";
	String yearto = "";
	
	datefrom = datefrom.replaceAll("/", "");
	dateto = dateto.replaceAll("/", "");
	
	dayfrom = datefrom.substring(0, 2);
	monthfrom = datefrom.substring(2, 4);
	yearfrom = datefrom.substring(4);
	
	dayto = dateto.substring(0, 2);
	monthto = dateto.substring(2, 4);
	yearto = dateto.substring(4);
	
	System.out.println("dayfrom = "+dayfrom);
	System.out.println("monthfrom = "+monthfrom);
	System.out.println("yearfrom = "+yearfrom);
	
	System.out.println("dayto = "+dayto);
	System.out.println("monthto = "+monthto);
	System.out.println("yearto = "+yearto);
	
	int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	DBConnectionManager connMgr;
	String connectionPool;
	SystemLogManager sysLogMgr;
	
	connectionPool = ConfigManager.getInstance().getConnectionPool();
	connMgr = DBConnectionManager.getInstance();
	String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
	if (timeOutValue != null) {
		try {
			TIMEOUT = Integer.parseInt(timeOutValue);
		} catch (NumberFormatException e) {}
	}
	sysLogMgr = SystemLogManager.getInstance();
	Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
	System.out.println("Getting database connection.");
	System.out.println("Connection is good at "+conn);
	
	//Construct the SQL statement
    System.out.println("Preparing sql statement.");
	String strSQL="SELECT * FROM TBLSYSLOG WHERE DATE1 >= TO_DATE( '"+yearfrom+"-"+monthfrom+"-"+dayfrom+"', 'YYYY-MM-DD hh:mi:ss AM' ) AND DATE1 <= TO_DATE( '"+yearto+"-"+monthto+"-"+dayto+"', 'YYYY-MM-DD hh:mi:ss AM' ) + 1 AND USERID = '"+recordsearch+"' ORDER BY DATE1 ASC";
    PreparedStatement pstmtQuery = null;
    System.out.println("The sql query is "+strSQL);
    System.out.println("Executing sql statement.");
   //ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE
    System.out.println("Getting sql query results.");
    //Run the SQL statement on database
    try {
		pstmtQuery =  conn.prepareStatement(strSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		rsrecord = pstmtQuery.executeQuery();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
    System.out.println(rsrecord);
    
    String activityname = "";
	String date1= "";
	String userfield1= "";
	String userfield2= "";
	
	System.out.println("The data acquired is :");
	try {
		while (rsrecord.next()){
		
		activityname = rsrecord.getString("ACTIVITYNAME");
		date1 = dateformat.format(rsrecord.getTimestamp("DATE1"));
		
		userfield1 = rsrecord.getString("USERFIELD1");
		userfield2 = rsrecord.getString("USERFIELD2");
		if (userfield1 == null){
			userfield1 = "";
		}
		if (userfield2 == null){
			userfield2 = "";
		}
		
		System.out.println("The date is "+date1);
		System.out.println("The activity name is "+activityname);
		System.out.println("The userfield1 is "+userfield1);
		System.out.println("The userfield2 is "+userfield2);
		
		recordresult.add(date1);
		recordresult.add(activityname);
		recordresult.add(userfield1);
		recordresult.add(userfield2);
		
		
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		System.out.println("SecAuditTrailHome rsrecord extraction error.");
	}
	finally {
		try {
			pstmtQuery.close();
			connMgr.freeConnection(connectionPool, conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	return recordresult;
		
}
	

	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
